#!/usr/bin/python2
#-*- coding=utf-8 -*-

import os
import json
import time
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from email.header import Header
import sys
import tarfile
import argparse
from SqlManager import SqlManager
from collections import defaultdict

import sys
reload(sys)
sys.setdefaultencoding('utf8')

#SENDER = 'david_xcy@163.com'
SENDER = '191130863@qq.com'
RECEIVER = ['191130863@qq.com','miaozhuo@thinkygame.com']
#RECEIVER = ['191130863@qq.com', 'david_xcy@163.com']
SUBJECT = r'{0} {1} 统计信息'
#SMTPSERVER = 'smtp.163.com'
SMTPSERVER = 'smtp.qq.com'
SMTPPORT = 465
#MAIL_PASSWORD = 'flzx6qc'
MAIL_PASSWORD = 'rvnysuxjltirbhia'

SEC_PER_DAY = 24 * 60 * 60

def send_mail(title, send_str, files, del_file = True):
    msg = MIMEMultipart()
    msg['Subject'] = Header(title, 'utf-8')
    msg['From'] = 'server<%s>' %SENDER
    msg['To'] = ';'.join(RECEIVER)
    msg.attach(MIMEText(send_str, 'plain', 'utf-8'))

    #add attachment
    for path in files:
        if not os.path.isfile(path):
            continue
        attachment = MIMEApplication(open(path, 'rb').read())
        attachment.add_header('Content-Disposition', 'attachment', filename='{0}'.format(os.path.basename(path)))
        msg.attach(attachment)
        del_file and os.remove(path)

    try:
        #smtp = smtplib.SMTP()
        #smtp.connect(SMTPSERVER)
        smtp = smtplib.SMTP_SSL(SMTPSERVER, SMTPPORT)
        smtp.login(SENDER, MAIL_PASSWORD)
        smtp.sendmail(SENDER, RECEIVER, msg.as_string())
    except Exception as e:
        print("send mail to %s error!" % ";".join(RECEIVER))
        print(e)
    smtp.quit()

def tar(filelist, path_name):
    tf = tarfile.open(path_name, "w:gz")

    for file_name in filelist:
        tf.add(file_name)

    tf.close()

def total_data(date):
    total_sql = '''
select t.totalRegister, c.currentRegister, r.currentLogin from
    (select count(uid) totalRegister from PlayerRegister where AccountType != 4 and EventTime <= '{0} 23:59:59') t,
    (select count(uid) currentRegister from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}') c,
    (select count(distinct uid) currentLogin from PlayerLogin where AccountType != 4 and EventTime regexp '^{0}') r'''.format(date)

    data = {}
    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = total_sql)

    if not result:
        return data

    data[date] = {'总注册'.decode('utf-8'): (result[0][0] and [result[0][0]] or [0])[0],
                  '当天总注册'.decode('utf-8'): (result[0][1] and [result[0][1]] or [0])[0],
                  '当天总登入'.decode('utf-8'): (result[0][2] and [result[0][2]] or [0])[0]}

    return data


def first_order_login(dates):
    '''一阶登入比'''
    sql = '''select coalesce(xx.ucount, 'total') ucount, count(*)  from (select uid, count(*) ucount from ('''

    for val in dates[1:]:
        sql += ''' select distinct(uid), if(uid, '{0}', null) date from PlayerLogin where EventTime regexp '^{0}' and uid in
            (select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{1}')''' .format(val, dates[0])

        sql += ''' union'''
    sql = sql[:-5] + ''') as x group by uid) as xx group by xx.ucount with rollup'''

    sql_mgr = SqlManager()
    result = sql_mgr.queryTable(sql = sql)

    #print result, result[-1][0] == 'total', result[0][0] == '1'
    if result and result[-1][0] == 'total':
        if result[0][0] == '1':
            return round(100.0 - result[0][1] * 100.0 / result[-1][1], 1)
        else:
            return 100.0

    return 0.0

def second_order_login(dates):
    '''二阶登入比'''
    sql_mgr = SqlManager()
    drop_tmp = '''drop table if exists tmp'''
    result = sql_mgr.executeSql(sql = drop_tmp)
    if not result:
        return 0;

    create_tmp = '''
create table tmp(uid bigint unsigned) select xx.uid from(
    select x.uid, count(*) ucount from (
    '''

    first_dates = dates[1:7]
    for val in first_dates:
        create_tmp += '''
        select distinct(uid), if(uid, '{0}', null) date from PlayerLogin where EventTime regexp '^{0}' and uid in(select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{1}') union'''.format(val, dates[0])

    create_tmp = create_tmp[:-5] + ''') as x group by x.uid) as xx where xx.ucount >= 2'''
    result = sql_mgr.executeSql(sql = create_tmp)
    if not result:
        result = sql_mgr.executeSql(sql = drop_tmp)
        return 0;

    query_sql = '''
select x.useful / t.total from (select count(uid) total from tmp) as t, (
    select count(distinct l.uid) useful from (
    '''

    second_dates = dates[-7:]
    for val in second_dates:
        query_sql += '''
        select distinct(uid) from PlayerLogin where EventTime regexp '^{0}' and uid in (select uid from tmp) union'''.format(val)

    query_sql = query_sql[:-5] + ''')as l ) as x;'''

    rate = 0.0
    result = sql_mgr.queryTable(sql = query_sql)
    if result and result[0]:
        rate = round((result[0][0] and [result[0][0]] or [0])[0] * 100, 1)

    sql_mgr.executeSql(sql = drop_tmp)

    return rate

def player_retetion(dates):
    sql = '''
select h.retention , h.retention / r.total from (
    select count(uid) total from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}'
)as r, (
    select count(distinct uid) retention from PlayerLogin where EventTime regexp '^{1}' and uid in(
        select uid from PlayerRegister where AccountType != 4 and EventTime regexp '^{0}')
)as h
    '''

    data = defaultdict(dict)
    sql_mgr = SqlManager()

    #for k, v in enumerate(reversed(dates[:-1]), 1):
    for val in dates[:-1]:
        result = sql_mgr.queryTable(sql = sql.format(val, dates[-1]))
        if not result:
            data[val] = '0 ( 0 )'

        else:
            retention = (result[0][0] and [result[0][0]] or [0])[0]
            rate = round((result[0][1] and [result[0][1]] or [0])[0] * 100, 1)
            data[val][dates[-1]] = '{0} ({1} %)'.format(retention, rate)

    return data


def query_oss_data(timestamp, file_name):

    timestamps = [val for val in xrange(timestamp - 13 * SEC_PER_DAY, timestamp, SEC_PER_DAY)]
    timestamps.append(timestamp)
    dates = []
    for val in timestamps:
        dates.append(time.strftime("%Y-%m-%d", time.localtime(val)))

    data = defaultdict(dict)

    if os.path.exists(file_name):
        with open(file_name, 'r') as f:
            data.update(json.load(f, encoding='utf-8'))

    if data.has_key(dates[-1]):
        return

    data.update(total_data(dates[-1]))
    retentions = player_retetion(dates[-7:])

    for k,v in retentions.iteritems():
        data.has_key(k) and data[k].update(v)

    if data.has_key(dates[-7]):
        data[dates[-7]].update({'一阶登入比'.decode('utf-8'): '{0} %'.format(first_order_login(dates[-7:]))})

    if data.has_key(dates[0]):
        data[dates[0]].update({'二阶登入比'.decode('utf-8'): '{0} %'.format(second_order_login(dates))})

    print(json.dumps(data, indent=4, encoding='utf-8', ensure_ascii=False))

    with open(file_name, 'w') as f:
        json.dump(data, f, indent=4, encoding='utf-8', ensure_ascii=False, sort_keys=True);


def argument_parser():
    parser = argparse.ArgumentParser()
    parser.add_argument('-m', '--mail', help='wether to send mail or not',  action='store_true')
    parser.add_argument('-s', '--subject', help='the subject of mail',  default='taptap')

    parser.add_argument('-f', '--file', help='the path about statistics file',  default='statistics.txt')

    parser.add_argument('-H', '--host', help='mysql host',  default='localhost')
    parser.add_argument('-P', '--PORT', help='mysql port',  default='3306')
    parser.add_argument('-u', '--user', help='mysql user\'s name',  default='root')
    parser.add_argument('-p', '--password', help='mysql user\'s passwrod', required=True)
    parser.add_argument('-D', '--database', help='database name',  default='oss')
    parser.add_argument('-c', '--character', help='the setting about mysql database\'s character', default='utf8')

    group = parser.add_mutually_exclusive_group(required=True)
    group.add_argument('-d', '--days', help='collect the statistics information from which day to yesterday', type=int)
    group.add_argument('-t', '--timestamp', help='collect the statistics information about the day of the timestamp', type=int)
    group.add_argument('-y', '--yesterday', help='collect the yesterday\'s statistics information', action='store_true')

    return parser.parse_args()

def main():
    args = argument_parser();
    sql_mgr = SqlManager()
    sql_mgr.setConnect(args.database, args.host, args.user, args.password, args.character)

    timestamp = (args.timestamp and [args.timestamp] or [time.time() - SEC_PER_DAY])[0]
    count = (args.days and [args.days] or [1])[0]

    for elem in xrange(count - 1, -1, -1):
        query_oss_data(int(timestamp - SEC_PER_DAY * elem), args.file)

    if args.mail:
        send_mail(SUBJECT.format(time.strftime("%Y/%m/%d ", time.localtime(timestamp)), args.subject), "" , [args.file], False)


if __name__ == "__main__":
    main()
